import psycopg2
from psycopg2 import sql


def get_db_connection():
    try:
        connection = psycopg2.connect(
            database="english_practice",
            user="my_root",  # 请替换为你的数据库用户名
            password="Test@123",
            host="117.72.107.144",
            port="5432"
        )
        return connection
    except psycopg2.Error as e:
        print(f"数据库连接错误: {e}")
        return None


# ========== 题目数据查询 ==========
def get_vocab_grammar_questions():
    conn = get_db_connection()
    if not conn:
        return []
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT q.id, q.question, o.option_id, o.option_text, q.correct_answer, q.explanation
            FROM questions q
            JOIN options o ON q.id = o.question_id
            ORDER BY q.id;
        """)
        rows = cur.fetchall()
        questions = {}
        for row in rows:
            q_id, question, opt_id, opt_text, ca, exp = row
            if q_id not in questions:
                questions[q_id] = {
                    "id": q_id,
                    "question": question,
                    "options": [],
                    "correctAnswer": ca,
                    "explanation": exp
                }
            questions[q_id]["options"].append({
                "id": opt_id,  # 确保此处逗号正确
                "text": opt_text
            })  # 确保字典键值对格式正确
        return list(questions.values())  # 第44行（假设此处为实际行号）

    except psycopg2.Error as e:
        print(f"查询错误: {e}")
    finally:
        if conn:
            conn.close()
    return []


def get_dialogue_cloze_questions():
    # 假设与词汇语法题结构相同，实际需根据表结构调整
    return get_vocab_grammar_questions()


# 以下为其他函数（需保持相同缩进级别）
def get_reading_questions():
    conn = get_db_connection()
    if not conn:
        return []
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT rp.id, rp.passage, rq.id, rq.question, rq.options, rq.correct_answer, rq.explanation
            FROM reading_passages rp
            JOIN reading_questions rq ON rp.id = rq.passage_id
            ORDER BY rp.id, rq.id;
        """)
        passages = {}
        for row in cur.fetchall():
            rp_id, passage, rq_id, question, options, ca, exp = row
            if rp_id not in passages:
                passages[rp_id] = {
                    "id": rp_id,
                    "passage": passage,
                    "questions": []
                }
            passages[rp_id]["questions"].append({
                "id": rq_id,
                "question": question,
                "options": [{"id": f"{chr(65 + i)}", "text": opt} for i, opt in enumerate(options)],
                "correctAnswer": ca,
                "explanation": exp
            })
        return list(passages.values())
    except psycopg2.Error as e:
        print(f"查询阅读理解错误: {e}")
    finally:
        if conn:
            conn.close()
    return []
def get_writing_topics():
    conn = get_db_connection()
    try:
        cur = conn.cursor()
        cur.execute("SELECT * FROM writing_topics")
        columns = [col.name for col in cur.description]
        return [dict(zip(columns, row)) for row in cur.fetchall()]
    except psycopg2.Error as e:
        print(f"查询写作题目错误: {e}")
    finally:
        if conn: conn.close()
    return []

# ========== 用户数据操作 ==========
def submit_answer(data):
    conn = get_db_connection()
    if not conn: return False
    try:
        cur = conn.cursor()
        query = sql.SQL("""
            INSERT INTO user_answers (user_id, question_type, question_id, user_answer)
            VALUES (%s, %s, %s, %s)
        """)
        cur.execute(query, (
            data["userId"],
            data["questionType"],
            data["questionId"],
            data["userAnswer"]
        ))
        conn.commit()
        return True
    except psycopg2.Error as e:
        print(f"提交答案错误: {e}")
        conn.rollback()
    finally:
        if conn: conn.close()
    return False

def get_wrong_answers(user_id):
    conn = get_db_connection()
    if not conn: return []
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT * FROM wrong_answers
            WHERE user_id = %s
        """, (user_id,))
        columns = [col.name for col in cur.description]
        return [dict(zip(columns, row)) for row in cur.fetchall()]
    except psycopg2.Error as e:
        print(f"查询错题错误: {e}")
    finally:
        if conn: conn.close()
    return []

def save_wrong_answer(data):
    conn = get_db_connection()
    if not conn: return False
    try:
        cur = conn.cursor()
        query = sql.SQL("""
            INSERT INTO wrong_answers (user_id, question_id, question_type, question, options, correct_answer, explanation, user_answer)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
            ON CONFLICT (user_id, question_id, question_type) DO UPDATE
            SET question = EXCLUDED.question, options = EXCLUDED.options, explanation = EXCLUDED.explanation, user_answer = EXCLUDED.user_answer
        """)
        cur.execute(query, (
            data["userId"],
            data["id"],
            data["type"],
            data["question"],
            data["options"],  # 假设options是列表格式
            data["correctAnswer"],
            data["explanation"],
            data["userAnswer"]
        ))
        conn.commit()
        return True
    except psycopg2.Error as e:
        print(f"保存错题错误: {e}")
        conn.rollback()
    finally:
        if conn: conn.close()
    return False

def delete_wrong_answer(user_id, question_id):
    conn = get_db_connection()
    if not conn: return False
    try:
        cur = conn.cursor()
        cur.execute("""
            DELETE FROM wrong_answers
            WHERE user_id = %s AND question_id = %s
        """, (user_id, question_id))
        conn.commit()
        return cur.rowcount > 0
    except psycopg2.Error as e:
        print(f"删除错题错误: {e}")
        conn.rollback()
    finally:
        if conn: conn.close()
    return False

def clear_wrong_answers(user_id):
    conn = get_db_connection()
    if not conn: return False
    try:
        cur = conn.cursor()
        cur.execute("""
            DELETE FROM wrong_answers
            WHERE user_id = %s
        """, (user_id,))
        conn.commit()
        return cur.rowcount > 0
    except psycopg2.Error as e:
        print(f"清空错题错误: {e}")
        conn.rollback()
    finally:
        if conn: conn.close()
    return False

def get_user_progress(user_id):
    conn = get_db_connection()
    if not conn: return {}
    try:
        cur = conn.cursor()
        cur.execute("""
            SELECT * FROM user_progress
            WHERE user_id = %s
        """, (user_id,))
        row = cur.fetchone()
        if not row: return {}
        columns = [col.name for col in cur.description]
        return dict(zip(columns, row))
    except psycopg2.Error as e:
        print(f"查询进度错误: {e}")
    finally:
        if conn: conn.close()
    return {}

def save_user_progress(user_id, progress_data):
    conn = get_db_connection()
    if not conn: return False
    try:
        cur = conn.cursor()
        query = sql.SQL("""
            INSERT INTO user_progress (user_id, vocabulary_grammar, dialogue_cloze, reading, writing)
            VALUES (%s, %s, %s, %s, %s)
            ON CONFLICT (user_id) DO UPDATE
            SET vocabulary_grammar = EXCLUDED.vocabulary_grammar,
                dialogue_cloze = EXCLUDED.dialogue_cloze,
                reading = EXCLUDED.reading,
                writing = EXCLUDED.writing
        """)
        cur.execute(query, (
            user_id,
            progress_data.get("vocabularyGrammar"),
            progress_data.get("dialogueCloze"),
            progress_data.get("reading"),
            progress_data.get("writing")
        ))
        conn.commit()
        return True
    except psycopg2.Error as e:
        print(f"保存进度错误: {e}")
        conn.rollback()
    finally:
        if conn: conn.close()
    return False